/********************************************************************************
 * Project Name		[JavaEE_JDBC]
 * File Name     	[TestMetaData.java]
 * Creation Date 	[2015-01-01]
 * 
 * Copyright© ge.y.yang@gmail.com All Rights Reserved
 * 
 * Work hard, play harder, think big and keep fit
 ********************************************************************************/
package jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import utils.jdbc.JdbcUtils;

/**
 * java.sql.DatabaseMetaData<br/>
 * java.sql.ParameterMetaData<br/>
 * java.sql.ResultSetMetaData<br/>
 * 
 * @author 不落的太阳(Sean Yang aka ShortPeace)
 * @version 1.0
 * @since jdk 1.8
 * 
 */
public class TestMetaData {

	private Connection connection;
	private PreparedStatement statement;
	private ResultSet resultSet;

	@Before
	public void init() throws Exception {
		// 必须这样写URL, 否则得不到ParameterMetaData
		connection = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/jdbc?useUnicode=true&generateSimpleParameterMetadata=true", "root",
				"123456");
	}

	/**
	 * Database MetaData[数据库相关的元数据]
	 * 
	 * @throws Exception
	 */
	@Test
	public void testDatabaseMetadata() throws Exception {
		DatabaseMetaData databaseMetaData = connection.getMetaData();
		System.out.println("数据库" + databaseMetaData.getURL() + "的元数据如下:");

		// 显示元数据信息
		System.out.println("驱动: " + databaseMetaData.getDriverName());
		System.out.println("驱动版本号: " + databaseMetaData.getDriverVersion());
		System.out.println("登陆用户名: " + databaseMetaData.getUserName());
		System.out.println("数据库产品名: " + databaseMetaData.getDatabaseProductName());
		System.out.println("数据库产品版本号: " + databaseMetaData.getDatabaseProductVersion());
		System.out.println("支持的SQL关键字: ");
		System.out.println(databaseMetaData.getSQLKeywords());
		System.out.println("操作数字的函数: ");
		System.out.println(databaseMetaData.getNumericFunctions());
		System.out.println("操作字符串的函数: ");
		System.out.println(databaseMetaData.getStringFunctions());
		System.out.println("系统函数: ");
		System.out.println(databaseMetaData.getSystemFunctions());
		System.out.println("时间和日期函数: ");
		System.out.println(databaseMetaData.getTimeDateFunctions());
		System.out.println("支持批处理: " + databaseMetaData.supportsBatchUpdates());
		System.out.println("支持保存点: " + databaseMetaData.supportsSavepoints());
		System.out.println("支持事务处理: " + databaseMetaData.supportsTransactions());
		System.out.println("事务隔离级别: " + databaseMetaData.getDefaultTransactionIsolation());
	}

	/**
	 * ParameterMetaData[参数相关元数据]
	 * 
	 * @throws Exception
	 */
	@Test
	public void testParameterMetaData() throws Exception {
		String sql = "SELECT * FROM User WHERE username = ? AND password = ?";
		statement = connection.prepareStatement(sql);
		statement.setString(1, "Sean");
		statement.setString(2, "12345");

		ParameterMetaData parameterMetaData = statement.getParameterMetaData();
		int parameterCount = parameterMetaData.getParameterCount();
		for (int i = 1; i <= parameterCount; i++) {
			// 获得指定参数的Java类型名称
			System.out.print(parameterMetaData.getParameterClassName(i) + "\t");
			// 获得指定参数类型
			System.out.print(parameterMetaData.getParameterType(i) + "\t");
			// 获得指定参数对应的数据库类型名
			System.out.println(parameterMetaData.getParameterTypeName(i));
		}
	}

	/**
	 * ResultSetMetaData[结果集相关的元数据]
	 * 
	 * @throws Exception
	 */
	@Test
	public void testResultSetMetaData() throws Exception {
		String sql = "SELECT * FROM User";
		statement = connection.prepareStatement(sql);
		resultSet = statement.executeQuery();

		StringBuilder builder = new StringBuilder();

		// 获得结果集元数据
		ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
		// 获得表中字段的个数
		int count = resultSetMetaData.getColumnCount();
		for (int i = 1; i <= count; i++) {
			// 获得元数据信息
			builder.append(resultSetMetaData.getColumnLabel(i) + "\t");
			builder.append(resultSetMetaData.getColumnName(i) + "\t");
			builder.append(resultSetMetaData.getColumnType(i) + "\t");
			builder.append(resultSetMetaData.getColumnClassName(i) + "\t");
			builder.append(resultSetMetaData.getColumnDisplaySize(i));
			builder.append("\n");
		}

		System.out.println(builder.toString());
	}

	@After
	public void destroy() {
		JdbcUtils.getInstance().close(resultSet, statement, connection);
	}
}
